package com.multitrack.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import com.multitrack.datatype.CurrLocation;
import com.multitrack.datatype.CurrLocationList;
import com.multitrack.datatype.Event;
import com.multitrack.datatype.EventData;
import com.multitrack.datatype.GPS;
import com.multitrack.datatype.GPSList;
import com.multitrack.datatype.Identity;
import com.multitrack.datatype.IdentityList;
import com.multitrack.datatype.MemberGPS;
import com.multitrack.datatype.MyLocation;
import com.multitrack.datatype.SocketMessage;
import com.multitrack.datatype.TrackSelectArg;
import com.multitrack.datatype.UserInfo;

public class Sql {

	static final boolean DEBUG_MODE = true;
	static final int TRUE = 1;
	static final int FALSE = 0;
	private static final float RANGE = 20;

	Connection connect = null;
	PreparedStatement psmt;
	Statement state = null;
	ResultSet rs = null;

	String query = null;
	String sql = null;

	SocketMessage message;
	private ArrayList<String> registrationIds;
	private HashMap<String, Long> LastSendMessageTime = new HashMap<String, Long>();
	static int PORT;

	public Sql(int port) {
		PORT = port;
	}

	public Sql(int port, SocketMessage message) {
		PORT = port;
		this.message = message;
	}

	public void connect() {

		psmt = null;

		try {

			Class.forName("com.mysql.jdbc.Driver").newInstance();

		} catch (InstantiationException e) {

			e.printStackTrace();

		} catch (IllegalAccessException e) {

			e.printStackTrace();

		} catch (ClassNotFoundException e) {

			e.printStackTrace();
		}

		try {

			connect = DriverManager
					.getConnection(
							"jdbc:mysql://localhost/multitrack?useUnicode=true&characterEncoding=euckr",
							"root", "wkddls");
			printPort();
			SocketMessage.SQL_CONNECTION_SUCCESSFUL.print();

		} catch (SQLException e) {

			e.printStackTrace();
			printPort();
			SocketMessage.SQL_CONNECTION_FALSE.print();

		}
	}

	public SocketMessage checkId(String id) {

		query = "SELECT * FROM  member WHERE member_id = '" + id + "';";

		state = null;
		rs = null;

		try {

			state = connect.createStatement();
			state.executeQuery(query);

			rs = state.getResultSet();

			if (rs.first() == false) {
				printPort();
				System.out.println(SocketMessage.ID_NOT_FOUND.toString() + "("
						+ id + ")");

				return SocketMessage.ID_NOT_FOUND;

			}

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return SocketMessage.ERROR;
		}

		printPort();
		System.out.println(SocketMessage.ID_FOUND.toString() + "(" + id + ")");
		return SocketMessage.ID_FOUND;

	}

	public SocketMessage appJoin(UserInfo userInfo) {

		int agreement;

		if (userInfo.isAgree()) {
			agreement = TRUE;
		} else {
			agreement = FALSE;
		}

		state = null;

		query = "INSERT INTO member (member_id, member_name, gcm_id, agreement) VALUES ('"
				+ userInfo.getId()
				+ "','"
				+ userInfo.getName()
				+ "','"
				+ userInfo.getGcm_id() + "', '" + agreement + "');";

		System.out.println(query);

		try {

			state = connect.createStatement();

			if (state.executeUpdate(query) == 0) {
				printPort();
				SocketMessage.SQL_QUERY_FAIL.print();
				return SocketMessage.SQL_QUERY_FAIL;

			}

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return SocketMessage.SQL_QUERY_ERROR;
		}
		printPort();
		SocketMessage.SQL_QUERY_SUCCESS.print();
		return SocketMessage.SQL_QUERY_SUCCESS;

	}

	public SocketMessage recordLocation(MyLocation myLoc) throws SQLException {

		state = null;

		query = "INSERT INTO location (time, event_id, member_id, lat, lon, alt) "
				+ "VALUES ( '"
				+ myLoc.getGps().getTime()
				+ "', '"
				+ myLoc.getEvent_id()
				+ "', '"
				+ myLoc.getMember_id()
				+ "', '"
				+ myLoc.getGps().getLat()
				+ "', '"
				+ myLoc.getGps().getLon()
				+ "', '" + myLoc.getGps().getAlt() + "');";

		System.out.println(query);

		try {

			state = connect.createStatement();
			state.executeUpdate(query);

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return SocketMessage.SQL_QUERY_ERROR;
		}
		printPort();
		SocketMessage.SQL_QUERY_SUCCESS.print();

		checkLeaveMember(myLoc);

		return SocketMessage.SQL_QUERY_SUCCESS;

	}

	public SocketMessage checkLeaveMember(MyLocation loc) throws SQLException {
		state = null;
		rs = null;
		float[] results = new float[2];

		query = "SELECT location.lat, location.lon, location.alt FROM event, location WHERE event.event_id = '"
				+ loc.getEvent_id()
				+ "' AND event.leader_id = location.member_id;";

		System.out.println(query);

		try {

			state = connect.createStatement();
			state.executeQuery(query);

			rs = state.getResultSet();

			while (rs.next()) {

				MyLocation.distanceBetween(rs.getDouble("lat"), rs
						.getDouble("lon"), loc.getGps().getLat(), loc.getGps()
						.getLon(), results);
			}

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return SocketMessage.SQL_QUERY_ERROR;
		}

		printPort();
		SocketMessage.SQL_QUERY_SUCCESS.print();

		if (results[0] > RANGE) {
			String memberName = getMembername(loc.getMember_id());
			push(loc.getEvent_id(), memberName, message);
		}
		return SocketMessage.SQL_QUERY_SUCCESS;

	}

	public IdentityList responseEventList() {

		state = null;
		rs = null;

		String eventId = null;
		String eventName = null;

		query = "SELECT event_name, event_id FROM event WHERE event_pw = '';";

		System.out.println(query);

		try {

			state = connect.createStatement();
			state.executeQuery(query);

			rs = state.getResultSet();

			IdentityList eventData = new IdentityList();

			while (rs.next()) {

				eventId = rs.getString("event_id");
				eventName = rs.getString("event_name");

				eventData.add(new Identity(eventId, eventName));

				System.out.println(eventData.get(eventData.size() - 1));

			}
			printPort();
			SocketMessage.SQL_QUERY_SUCCESS.print();
			return eventData;

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return null;

		}

	}

	public void push(String eventId, String userName, SocketMessage msg) {

		state = null;
		rs = null;
		registrationIds = new ArrayList<String>();
		query = "SELECT member.member_name, member.gcm_id FROM event_member, member WHERE event_member.event_id = '"
				+ eventId + "' AND event_member.member_id = member.member_id;";

		System.out.println(query);

		try {

			state = connect.createStatement();
			state.executeQuery(query);

			rs = state.getResultSet();

			while (rs.next()) {

				registrationIds.add(rs.getString("gcm_id"));
				System.out.println("RegID :" + rs.getString("gcm_id"));

			}
			GCMSender sender = new GCMSender();
			switch (msg) {
			case EVENT_JOIN:
				sender.setMessage(userName + " 님이 방에 입장하셨습니다.");
				break;
			case EVENT_QUIT:
				sender.setMessage(userName + " 님이 방을 나갔습니다.");
				break;
			case CHANGE_LEADER:
				sender.setMessage(userName + " 님으로 방장이 변경되었습니다");
				break;
			case BAN_MEMBER:
				sender.setMessage(userName + " 님이 강퇴되었습니다.");
				break;
			case SEND_DESTINATION:
				sender.setMessage("목적지가 설정되었습니다.");
				break;
			case RECORD_LOCATION:
				sender.setMessage(userName + " 님이 이탈했습니다.");
				if (LastSendMessageTime.get(userName) != null) {
					if ((System.currentTimeMillis() / 1000 - LastSendMessageTime
							.get(userName)) < 60) {
						LastSendMessageTime.put(userName,
								System.currentTimeMillis() / 1000);
						sender.sendMessage(registrationIds);
						return;
					} else
						return;
				} else {
					LastSendMessageTime.put(userName,
							System.currentTimeMillis() / 1000);
					sender.sendMessage(registrationIds);
					return;
				}
			}
			sender.sendMessage(registrationIds);
			System.out.println("GCM발송완료");
			// System.out.print(port + ": ");
			// SocketMessage.SQL_QUERY_SUCCESS.print();
			return;

		} catch (SQLException e) {
			System.out.print(PORT + ": ");
			SocketMessage.SQL_QUERY_ERROR.print();
			return;

		} catch (IllegalArgumentException e) {
			return;
		}
	}

	public IdentityList responseMemberList(String eventId) {

		state = null;
		rs = null;

		query = "SELECT member.member_id, member.member_name FROM event, member WHERE event.event_id = '"
				+ eventId
				+ "' AND leader_id = member.member_id UNION ( SELECT member.member_id, member.member_name FROM event_member, member WHERE event_member.event_id = '"
				+ eventId + "' AND event_member.member_id = member.member_id);";

		System.out.println(query);

		try {

			state = connect.createStatement();
			state.executeQuery(query);

			rs = state.getResultSet();

			IdentityList memberData = new IdentityList();

			while (rs.next()) {

				memberData.add(new Identity(rs.getString("member_id"), rs
						.getString("member_name")));

			}

			printPort();
			SocketMessage.SQL_QUERY_SUCCESS.print();
			return memberData;

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return null;

		}
	}

	// TODO: TEST
	@SuppressWarnings("finally")
	public MemberGPS responseMemberTrack(TrackSelectArg arg) {
		String TAG = "responseMemberTrack";
		SocketMessage msg = null;
		MemberGPS memberGPS = new MemberGPS();
		sql = "SELECT location.* FROM location WHERE event_id = ? AND member_id = ? AND time > ?";
		printSQL(TAG, sql);

		try {
			psmt = connect.prepareStatement(sql);
			psmt.setString(1, arg.getEvent_id());
			psmt.setString(2, arg.getMember_id());
			psmt.setLong(3, arg.getTime());
			rs = psmt.executeQuery();

			memberGPS.setId(arg.getMember_id());
			while (rs.next()) {
				GPS gps = new GPS();
				gps.setTime(rs.getLong("time"));
				gps.setLat(rs.getDouble("lat"));
				gps.setLon(rs.getDouble("lon"));
				gps.setAlt(rs.getDouble("alt"));
				memberGPS.add(gps);
			}
			msg = SocketMessage.SQL_QUERY_SUCCESS;
		} catch (SQLException e) {
			msg = SocketMessage.SQL_QUERY_ERROR;
		} finally {
			printPort();
			printMessage(TAG, msg, arg);
			return memberGPS;
		}
	}

	public CurrLocationList responseAllMemberCurrLoc(String eventId) {

		state = null;
		rs = null;

		// query =
		// "SELECT member.member_id, member.member_name, location.time, location.member_id, location.lat, location.lon, location.alt "
		// + "FROM location, member WHERE location.event_id = '"
		// + eventId
		// +
		// "' AND member.member_id = location.member_id ORDER BY member.member_name;";

		query = "SELECT time, L1.member_id,  member_name, lat, lon, alt"
				+ " FROM location L1, member L3 WHERE event_id ='"
				+ eventId
				+ "' AND time >= all(SELECT time FROM location L2 WHERE event_id='"
				+ eventId
				+ "' AND L1.member_id = L2.member_id) AND L1.member_id = L3.member_id";

		System.out.println(query);

		try {

			state = connect.createStatement();
			state.executeQuery(query);

			rs = state.getResultSet();

			CurrLocationList currLocList = new CurrLocationList();

			while (rs.next()) {

				Identity member = new Identity(rs.getString("member_id"),
						rs.getString("member_name"));

				GPS gps = new GPS(rs.getLong("time"), rs.getDouble("lat"),
						rs.getDouble("lon"), rs.getDouble("alt"));

				currLocList.add(new CurrLocation(member, gps));

			}

			printPort();
			SocketMessage.SQL_QUERY_SUCCESS.print();
			return currLocList;

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return null;

		}

	}

	public MemberGPS responseMoveLocation(EventData eventData) {

		state = null;
		rs = null;

		query = "SELECT member.member_id, member.member_name, location.time, location.member_id, location.lat, location.lon, location.alt "
				+ "FROM location, member WHERE location.event_id = '"
				+ eventData.getEventId()
				+ "' AND member.member_id = '"
				+ eventData.getMemberId() + "' ORDER BY member.member_name;";

		System.out.println(query);

		try {

			state = connect.createStatement();
			state.executeQuery(query);

			rs = state.getResultSet();

			String member_id = null;
			MemberGPS memberGPS = new MemberGPS();
			GPSList log = new GPSList();

			while (rs.next()) {

				member_id = rs.getString("member_id");
				log.add(new GPS(rs.getLong("time"), rs.getDouble("lat"), rs
						.getDouble("lon"), rs.getDouble("alt")));

				memberGPS = new MemberGPS(member_id, log);

			}

			printPort();
			SocketMessage.SQL_QUERY_SUCCESS.print();
			return memberGPS;

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return null;

		}

	}

	// Test Code
	public void memberClear() {

		state = null;

		query = "DELETE FROM member";

		try {

			state = connect.createStatement();
			state.executeQuery(query);

			printPort();
			SocketMessage.SQL_QUERY_SUCCESS.print();

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();

		}
	}

	/**
	 * 2012.09.16 BBJ
	 */
	@SuppressWarnings("finally")
	public SocketMessage executeCreateEventTransaction(Event event) {
		String TAG = "executeCreateEvent";
		SocketMessage resultMsg = null;

		try {
			connect.setAutoCommit(false);

			resultMsg = insertEvent(event);
			checkSQLFailAndThrows(resultMsg);

			resultMsg = insertEventMember(event.getId(), event.getLeaderId());
			checkSQLFailAndThrows(resultMsg);

			connect.commit();

		} catch (SQLException e) {
			connect.rollback();

			resultMsg = SocketMessage.SQL_QUERY_ERROR;
			e.printStackTrace();

		} finally {
			conncetClose();
			printPort();
			printMessage(TAG, resultMsg);
			return resultMsg;

		}

	}

	/**
	 * 2012.09.16 BBJ
	 */
	private void conncetClose() {

		try {
			if (rs != null) {
				rs.close();
			}

			if (state != null) {
				state.close();
			}

			if (psmt != null) {
				psmt.close();
			}

			if (connect != null) {
				connect.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 2012.09.16 BBJ
	 */
	private SocketMessage insertEvent(Event event) throws SQLException {

		final String TAG = "insertEvent";

		sql = "INSERT INTO event (leader_id, event_id, event_name, event_pw) VALUES (?, ?, ?, ?)";

		printSQL(TAG, sql);

		psmt = connect.prepareStatement(sql);

		psmt.setString(1, event.getLeaderId());
		psmt.setString(2, event.getId());
		psmt.setString(3, event.getName());

		String password = null;

		if (event.getPw() == null) {
			psmt.setString(4, "");
		} else {
			psmt.setString(4, password);
		}

		SocketMessage resultQuery = null;

		if (psmt.executeUpdate() == 0) {
			resultQuery = SocketMessage.SQL_QUERY_FAIL;
		} else {
			resultQuery = SocketMessage.SQL_QUERY_SUCCESS;
		}

		printPort();
		printMessage(TAG, resultQuery, event);

		return resultQuery;
	}

	/*
	 * 2012.09.16 BBJ
	 */
	private SocketMessage insertEventMember(String event_id, String member_id)
			throws SQLException {

		String TAG = "insertEventMember";
		String sql = null;
		SocketMessage resultQuery = null;

		sql = "INSERT INTO event_member (event_id, member_id, time) VALUES (?, ?, NOW())";

		printSQL(TAG, sql);

		psmt = connect.prepareStatement(sql);
		psmt.setString(1, event_id);
		psmt.setString(2, member_id);

		if (psmt.executeUpdate() == 0) {
			resultQuery = SocketMessage.SQL_QUERY_FAIL;
		} else {
			resultQuery = SocketMessage.SQL_QUERY_SUCCESS;
		}

		printPort();
		printMessage(TAG, resultQuery, event_id, member_id);

		return resultQuery;

	}

	/*
	 * 2012.09.16 BBJ
	 */
	private SocketMessage executeDeleteSQL(String table, String attribute,
			String value) throws SQLException {
		String TAG = "executeDeleteSQL";
		String sql = null;
		SocketMessage resultQuery = null;

		sql = "DELETE " + "FROM " + table + " " + "WHERE " + attribute + " = "
				+ value + ";";

		if (DEBUG_MODE) {
			printSQL(TAG, sql);
		}

		state = connect.createStatement();

		if (state.executeUpdate(sql) == FALSE) {
			resultQuery = SocketMessage.SQL_QUERY_FAIL;
		} else {
			resultQuery = SocketMessage.SQL_QUERY_SUCCESS;
		}

		printPort();
		printMessage(TAG, resultQuery, value);

		return resultQuery;

	}

	/*
	 * 12.09.19 - BBJ
	 */
	@SuppressWarnings("finally")
	public Event responseEventInfo(EventData data) {

		final String TAG = "responseEventInfo";
		String id = null;
		if (data.getEventId() == null) {
			id = data.getMemberId();
			sql = "SELECT event.* "
					+ "FROM event, event_member "
					+ "WHERE event.event_id = event_member.event_id AND event_member.member_id = ?";
		} else {
			id = data.getEventId();
			sql = "SELECT event.* FROM event WHERE event_id = ?;";
		}

		printSQL(TAG, sql);

		Event event = null;
		SocketMessage msg = null;
		try {
			psmt = connect.prepareStatement(sql);
			psmt.setString(1, id);
			rs = psmt.executeQuery();
			if (rs.first()) {
				event = new Event();
				event.setId(rs.getString("event_id"));
				event.setName(rs.getString("event_name"));
				event.setPw(rs.getString("event_pw"));
				event.setLeaderId(rs.getString("leader_id"));
				event.setLat(rs.getInt("lat"));
				event.setLon(rs.getInt("lon"));
				msg = SocketMessage.SQL_QUERY_SUCCESS;
			} else {
				msg = SocketMessage.SQL_QUERY_FAIL;
			}

		} catch (SQLException e1) {
			msg = SocketMessage.SQL_QUERY_ERROR;
			e1.printStackTrace();
		} finally {
			conncetClose();
			printPort();
			printMessage(TAG, msg, id);
			return event;
		}
	}

	public Event responseEventInfo(Event event) {

		return null;
	}

	public SocketMessage joinEvent(EventData data) {

		state = null;
		rs = null;

		SocketMessage isInEvent = null;

		query = "SELECT * FROM  event_member WHERE event_member.member_id = '"
				+ data.getMemberId() + "';";

		try {
			state = connect.createStatement();
			state.executeQuery(query);

			rs = state.getResultSet();

			if (rs.first() == true) {

				printPort();
				System.out.println(SocketMessage.ID_FOUND.toString() + "("
						+ data.getMemberId() + ")");

				isInEvent = SocketMessage.ID_FOUND;

			} else {
				printPort();
				System.out.println(SocketMessage.ID_NOT_FOUND.toString() + "("
						+ data.getMemberId() + ")");

				isInEvent = SocketMessage.ID_NOT_FOUND;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return SocketMessage.ERROR;
		}

		if (isInEvent == SocketMessage.ID_FOUND) {
			return SocketMessage.ALREADY_JOIN_MEMBER_IN_EVENT;
		}

		query = "SELECT event_id, member_id FROM black_list WHERE event_id = '"
				+ data.getEventId() + "' and member_id = '"
				+ data.getMemberId() + "';";

		System.out.println(query);

		try {

			state = connect.createStatement();
			state.executeQuery(query);

			rs = state.getResultSet();
			if (rs.first() == true) {

				System.out.println(SocketMessage.BLACK_LIST.toString() + "("
						+ data.getEventId() + ", " + data.getMemberId() + ")");
				printPort();
				SocketMessage.BLACK_LIST.toString();
				return SocketMessage.BLACK_LIST;

			} else {
				printPort();
				SocketMessage.SQL_QUERY_SUCCESS.print();
			}

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return SocketMessage.ERROR;
		}

		query = "SELECT leader_id FROM event WHERE event_id = '"
				+ data.getEventId() + "';";

		System.out.println(query);

		String leaderId = null;

		try {

			state = connect.createStatement();
			state.executeQuery(query);

			rs = state.getResultSet();

			if (rs.first() == true) {

				leaderId = rs.getString("leader_id");

				printPort();
				SocketMessage.SQL_QUERY_SUCCESS.print();

			} else {
				printPort();
				SocketMessage.ERROR.toString();
				return SocketMessage.ERROR;
			}

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return SocketMessage.ERROR;
		}

		query = "INSERT INTO multitrack.event_member (event_id, member_id, time) VALUES ('"
				+ data.getEventId()
				+ "', '"
				+ data.getMemberId()
				+ "', NOW());";

		System.out.println(query);

		try {

			state = connect.createStatement();

			if (state.executeUpdate(query) == 0) {
				printPort();
				SocketMessage.SQL_QUERY_FAIL.toString();
				return SocketMessage.SQL_QUERY_FAIL;

			}

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return SocketMessage.SQL_QUERY_ERROR;
		}

		printPort();
		System.out.println(SocketMessage.SQL_QUERY_SUCCESS.toString() + "("
				+ data.getEventId() + ", " + data.getMemberId() + ")");
		
		String memberName = getMembername(data.getMemberId());
		push(data.getEventId(), memberName, SocketMessage.EVENT_JOIN);
		return SocketMessage.SQL_QUERY_SUCCESS;

	}

	public SocketMessage setDestination(GPS gps) {

		state = null;

		query = "UPDATE event SET event.lon = '" + gps.getLon()
				+ "', event.lat = '" + gps.getLat() + "';";

		System.out.println(query);

		try {

			state = connect.createStatement();
			state.executeUpdate(query);
			push("이벤트 ID가 들어갈 부분", "", SocketMessage.EVENT_JOIN);
			printPort();
			SocketMessage.SQL_QUERY_SUCCESS.print();
			return SocketMessage.SQL_QUERY_SUCCESS;

		} catch (SQLException e) {
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return SocketMessage.SQL_QUERY_ERROR;
		}

	}

	// 리더 변경
	@SuppressWarnings("finally")
	public String responseChangeLeader(Event event) {
		// TODO: need TEST
		String TAG = "responseChangeLeader";
		String leader_id = event.getLeaderId();
		SocketMessage msg = null;

		sql = "UPDATE event SET event.leader_id = ?";
		printSQL(TAG, sql);

		try {
			psmt = connect.prepareStatement(sql);
			psmt.setString(1, leader_id);

			if (psmt.executeUpdate(sql) == 0) {
				msg = SocketMessage.SQL_QUERY_FAIL;
			} else {
				msg = SocketMessage.SQL_QUERY_SUCCESS;
				push(event.getId(), getMembername(leader_id), this.message);
			}
		} catch (SQLException e) {
			msg = SocketMessage.SQL_QUERY_ERROR;
		} finally {
			printMessage(msg, leader_id);
			return leader_id;
		}

	}

	// black_list에 추가하는 메소드
	public SocketMessage insertBlackList(EventData eventData)
			throws SQLException {
		String TAG = "insertBlackList";

		state = null;
		rs = null;

		// black_list에 추가
		sql = "INSERT INTO black_list (black_list.event_id, black_list.member_id) VALUES (?, ?)";

		printSQL(TAG, sql);

		psmt = connect.prepareStatement(sql);

		psmt.setString(1, eventData.getEventId());
		psmt.setString(2, eventData.getMemberId());

		System.out.println(sql);

		SocketMessage resultQuery = null;

		if (psmt.executeUpdate() == 0) {
			resultQuery = SocketMessage.SQL_QUERY_FAIL;
		} else {
			resultQuery = SocketMessage.SQL_QUERY_SUCCESS;
		}

		return resultQuery;

	}

	// 멤버 강퇴
	public SocketMessage banMember(EventData eventData) throws SQLException {

		String TAG = "banMember";

		state = null;
		rs = null;

		// event_member 테이블에서 삭제
		sql = "DELETE FROM event_member WHERE event_member.event_id = ? AND event_member.member_id = ?";

		printSQL(TAG, sql);
		psmt = connect.prepareStatement(sql);
		psmt.setString(1, eventData.getEventId());
		psmt.setString(2, eventData.getMemberId());
		psmt.setString(3, eventData.getMemberId());

		SocketMessage resultQuery = null;

		if (psmt.executeUpdate() == 0) {
			resultQuery = SocketMessage.SQL_QUERY_FAIL;
		} else {
			String memberName = getMembername(eventData.getMemberId());
			push(eventData.getEventId(), memberName, SocketMessage.BAN_MEMBER);
			resultQuery = SocketMessage.SQL_QUERY_SUCCESS;
		}

		return resultQuery;

	}

	@SuppressWarnings("finally")
	public String executeBanMemberTransaction(EventData eventData) {

		String TAG = "executeBanMember";
		SocketMessage msg = null;
		String member_id = null;

		try {

			connect.setAutoCommit(false);

			msg = banMember(eventData);
			checkSQLFailAndThrows(msg);

			msg = insertBlackList(eventData);
			checkSQLFailAndThrows(msg);

			member_id = eventData.getMemberId();

			connect.commit();

		} catch (SQLException e) {

			connect.rollback();
			e.printStackTrace();

		} finally {

			conncetClose();
			printPort();
			printMessage(TAG, msg);
			return member_id;

		}
	}

	public SocketMessage checkEventMember(String id) {

		query = "SELECT * FROM  event_member WHERE member_id = '" + id + "';";

		state = null;
		rs = null;

		try {
			state = connect.createStatement();
			state.executeQuery(query);

			rs = state.getResultSet();

			if (rs.first() == true) {

				printPort();
				System.out.println(SocketMessage.ID_FOUND.toString() + "(" + id
						+ ")");

				return SocketMessage.ID_FOUND;

			}

		} catch (SQLException e) {
			e.printStackTrace();
			printPort();
			SocketMessage.SQL_QUERY_ERROR.print();
			return SocketMessage.ERROR;
		}

		printPort();
		System.out.println(SocketMessage.ID_NOT_FOUND.toString() + "(" + id
				+ ")");

		return SocketMessage.ID_NOT_FOUND;

	}

	@SuppressWarnings("finally")
	public SocketMessage eventQuit(EventData data) {

		String TAG = "eventQuit";
		SocketMessage resultMsg = null;
		int eventMemberCount;

		try {
			connect.setAutoCommit(false);

			resultMsg = executeDeleteSQL("event_member", "member_id", "'"
					+ data.getMemberId() + "'");
			checkSQLFailAndThrows(resultMsg);

			eventMemberCount = executeSelectCountSQL("event_member",
					"event_id", "'" + data.getEventId() + "'");
			
			if (eventMemberCount == 0) {
				resultMsg = executeDeleteSQL("event", "event_id",
						"'" + data.getEventId() + "'");
				checkSQLFailAndThrows(resultMsg);
			} else {
				resultMsg = executeUpdateSQL("event", "leader_id",
						"(SELECT member_id FROM event_member WHERE event_id = '"
								+ data.getEventId() + "' ORDER BY(time) ASC LIMIT 1)");
				checkSQLFailAndThrows(resultMsg);
			}
			connect.commit();

		} catch (SQLException e) {
			connect.rollback();

			resultMsg = SocketMessage.SQL_QUERY_ERROR;
			e.printStackTrace();

		} finally {
			conncetClose();
			printPort();
			printMessage(TAG, resultMsg);
			return resultMsg;

		}
	}

	/*
	 * 2012.10.26 BBJ
	 */
	private SocketMessage executeUpdateSQL(String table, String attribute,
			String value) throws SQLException {
		String TAG = "executeUpdateSQL";
		String sql = null;
		SocketMessage resultQuery = null;

		sql = "UPDATE " + table + " SET " + attribute + " = " + value + ";";

		if (DEBUG_MODE) {
			printSQL(TAG, sql);
		}

		state = connect.createStatement();

		if (state.executeUpdate(sql) == FALSE) {
			resultQuery = SocketMessage.SQL_QUERY_FAIL;
		} else {
			resultQuery = SocketMessage.SQL_QUERY_SUCCESS;
		}

		printPort();
		printMessage(TAG, resultQuery, value);

		return resultQuery;

	}

	/*
	 * 2012.10.26 BBJ
	 */
	private int executeSelectCountSQL(String table, String attribute,
			String value) throws SQLException {
		String TAG = "executeSelectCountSQL";
		String sql = null;

		sql = "SELECT COUNT(*) " + "FROM " + table + " " + "WHERE " + attribute
				+ " = " + value + ";";

		if (DEBUG_MODE) {
			printSQL(TAG, sql);
		}
		
		state = connect.createStatement();
		rs = state.executeQuery(sql);
		rs.first();
		
		return rs.getInt(1);
	}

	static void printSQL(String TAG, String sql) {
		if (DEBUG_MODE) {
			System.out.print("DEBUG: " + "[" + TAG + "] ");
			System.out.println(sql);
		}
	}

	static void checkSQLFailAndThrows(SocketMessage msg) throws SQLException {
		if (SocketMessage.SQL_QUERY_FAIL.equals(msg)) {
			throw new SQLException();
		}
	}

	static void printPort() {
		System.out.print(PORT + ": ");
	}

	static void printMessage(SocketMessage msg) {
		System.out.println(msg);
	}

	static void printMessage(SocketMessage msg, Exception e) {
		System.out.println(msg);
		e.printStackTrace();
	}

	static void printMessage(SocketMessage msg, String str1) {
		System.out.println(msg + "(" + str1 + ")");
	}

	static void printMessage(SocketMessage msg, String str1, String str2) {
		System.out.println(msg + "(" + str1 + ", " + str2 + ")");
	}

	static void printMessage(String TAG, SocketMessage msg) {
		System.out.println("[" + TAG + "] " + msg);
	}

	static void printMessage(String TAG, SocketMessage msg, Event event) {
		System.out.println("[" + TAG + "] " + msg + "(" + event.toString()
				+ ")");
	}

	private void printMessage(String TAG, SocketMessage msg, TrackSelectArg arg) {
		System.out.println("[" + TAG + "] " + msg + "(" + arg.toString() + ")");
	}

	static void printMessage(String TAG, SocketMessage msg, String str1) {
		System.out.println("[" + TAG + "] " + msg + "(" + str1 + ")");
	}

	static void printMessage(String TAG, SocketMessage msg, String str1,
			String str2) {
		System.out.println("[" + TAG + "] " + msg + "(" + str1 + ", " + str2
				+ ")");
	}

	public String getMembername(String MemberId) {
		state = null;
		rs = null;
		query = "SELECT member_name FROM member WHERE member_id='" + MemberId
				+ "'";
		System.out.println(query);
		String name = null;

		try {
			state = connect.createStatement();
			state.executeQuery(query);
			rs = state.getResultSet();
			while (rs.next()) {
				name = rs.getString("member_name");
			}
		} catch (SQLException e) {
			System.out.print(PORT + ": ");
			SocketMessage.SQL_QUERY_ERROR.print();
		}
		return name;
	}

}
